package com.example.myapplication.db;

import android.util.Log;

import com.example.myapplication.db.domain.AccountBean;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DBUtils {
    private static final String sshUser = "root";
    private static final String sshPassword = "i-uf69914kl3cvpco6topw";
    private static final String sshHost = "47.100.221.205";
    private static final int sshPort = 22;
    private static final String remoteHost = "localhost";
    private static final int remotePort = 3306;
    private static final String localHost = "127.0.0.1";
    private static final int localPort = 3306;
    private static final String dbUser = "root";
    private static final String dbPassword = "alliance";
    private static final String dbName = "bill_app";
    private static Session session = null;
    private static Connection connection = null;

    public static void getSSHConnection(){
        closeSession();
        Thread thread = new Thread(() -> {
            try {
                System.out.println("----------------------------Test Connect---------------------------");
                Properties sshProperties = new Properties();
                sshProperties.put("StrictHostKeyChecking", "no");
                JSch jsch = new JSch();
                session = jsch.getSession(sshUser, sshHost, sshPort);
                session.setPassword(sshPassword);
                session.setConfig(sshProperties);
                session.connect();
                System.out.println("Connected to SSH server");
            }catch (Exception e){
                e.printStackTrace();
            }
        });
        try {
            thread.start();
            thread.join();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        getSSHConnection();
        Thread thread = new Thread(() -> {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                int assinged_port = session.setPortForwardingL(localPort, remoteHost, remotePort);//将服务器端口和本地端口绑定，这样就能通过访问本地端口来访问服务器
                String url = "jdbc:mysql://" + localHost + ":" + assinged_port + "/" + dbName + "?characterEncoding=utf-8&&useSSL=false";
                System.out.println("url: " + url);
                connection = DriverManager.getConnection(url, dbUser, dbPassword);
                System.out.println("Connected to MySQL database");
            }catch (Exception e){
                e.printStackTrace();
            }
        });
        thread.start();
        try {
            thread.join();
        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeSession(){
        if (session != null){
            session.disconnect();
        }
    }

    public static <T> List<T> select(String sql, Class<T> cls){
        System.out.println(sql);
        Thread thread = new Thread(() -> {
            try {
                Connection connection = getConnection();
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                Method method = cls.getDeclaredMethod("toObject", ResultSet.class);
                DataManager.init();
                while (resultSet.next()){
                    DataManager.list.add(method.invoke(null, resultSet));
                }
                closeAll(connection, statement, resultSet);
            }catch (Exception e){
                e.printStackTrace();
            }
        });
        try {
            thread.start();
            thread.join();
        }catch (Exception e){
            e.printStackTrace();
        }
        return (List<T>) DataManager.list;
    }

    public static boolean update(String sql){
        System.out.println(sql);
        Thread thread = new Thread(() -> {
            try {
                Connection connection = getConnection();
                Statement statement = connection.createStatement();
                DataManager.init();
                DataManager.row = statement.executeUpdate(sql);
                closeAll(connection, statement, null);
            }catch (Exception e){
                e.printStackTrace();
            }
        });
        try {
            thread.start();
            thread.join();
        }catch (Exception e){
            e.printStackTrace();
        }
        return DataManager.row >= 1;
    }
}
